package DBLayer;

import ModelLayer.Invoice;
import ModelLayer.Product;
import ModelLayer.SalesOrder;
import Prototypes.DataLayer;
import Prototypes.SalesOrderResultSetModelBinder;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.SQLException;
import java.sql.Statement;

public class DBSalesContainer extends DataLayer implements ISalesContainer
{

    private static DBSalesContainer instance;

    public DBSalesContainer()
    {
        
        super();

    }

    public ArrayList getOrders()
    {
        try
        {
            String sql = "SELECT * FROM WSSalesOrder";
            Statement statement = dbConnection.createStatement();
            ResultSet results = statement.executeQuery(sql);

            SalesOrderResultSetModelBinder binder = new SalesOrderResultSetModelBinder();
            return binder.BindModels(results);
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBSalesContainer.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public ArrayList getOrders(int customerID)
    {
        try
        {
            String sql = "SELECT * FROM WSSalesOrder WHERE CustomerID = " + customerID;
            Statement statement = dbConnection.createStatement();
            ResultSet results = statement.executeQuery(sql);

            SalesOrderResultSetModelBinder binder = new SalesOrderResultSetModelBinder();
            return binder.BindModels(results);
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBSalesContainer.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    //gets the newest ID in the SalesOrder SQL table
    public int getNewestSalesOrderID()
    {
        
        ResultSet rs2 = null;
        int SalesOrderID = -1;
         String query = "SELECT MAX(SalesOrderID) FROM WSSalesOrder";

        System.out.println(query);

        try
        {
            Statement stmt = dbConnection.createStatement();
            stmt.setQueryTimeout(5);
            rs2 = stmt.executeQuery(query);

             if(rs2.next()) {
                SalesOrderID = rs2.getInt(1);
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            System.out.println("Insert exception in customer db; " + ex);
        }


        return SalesOrderID;

    }

    public boolean addSalesOrder(SalesOrder so)
    {
        int noOfAffectedRows = -1;




        String query = "INSERT INTO WSSalesOrder VALUES('"
                + so.getDate() + "','"
                + so.getDeliveryStatus() + "','"
                + so.getDeliveryDate() + "','"
                + so.getCustomerID() + "','"
                + so.getEmployeeID() + "')";

        System.out.println(query);

        try
        {
            Statement stmt = dbConnection.createStatement();
            stmt.setQueryTimeout(5);
            noOfAffectedRows = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (SQLException ex)
        {
            System.out.println("Insert exception in customer db; " + ex);
        }
        return noOfAffectedRows > 0;
    }

    public static DBSalesContainer getInstance()
    {
        if (instance == null)
        {
            instance = new DBSalesContainer();
        }
        return instance;
    }

}
